/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Controller;
import java.sql.*;
import java.util.ArrayList;
import Module.*;
/**
 *
 * @author Vg@rd
 */
public class UserController {

    private Database database = new Database();

    public boolean registerUser(User user){
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            String[] autogen = {"id"};
            PreparedStatement statement = conn.prepareStatement(""
                    + "insert into users values(DEFAULT,?,?)",autogen);
            statement.setString(1, user.getUsername());
            statement.setString(2, user.getPassword());
            statement.executeUpdate();
            conn.close();
            return true;

        }catch (Exception e) {
            System.err.println(e);
        }
        return false;
    }
    public boolean editUser(User user){
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement = conn.prepareStatement(""
                    + "update users set username=?, password=? where userId=?");
            statement.setString(1, user.getUsername());
            statement.setString(2, user.getPassword());
            statement.setInt(3, user.getId());
            statement.executeUpdate();
            conn.close();
            return true;

        }catch (Exception e) {
            System.err.println(e);
        }
        return false;

    }
    public boolean deleteUser(User user){
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement = conn.prepareStatement("delete from users where userId=?");
            statement.setInt(1, user.getId());
            statement.executeUpdate();
            conn.close();
            return true;
        }catch(Exception e){
            System.err.println(e);
        }
        return false;

    }

    public ArrayList<User> getAllUsers(){
        Connection conn = null;
        ArrayList<User> users = new ArrayList<User>();
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement = conn.prepareStatement("select * from users");
            ResultSet rs = statement.executeQuery();
            while(rs.next()){
                users.add(new User(rs.getInt(1),rs.getString(2),rs.getString(3)));
            }
            conn.close();
            return users;

        }catch(Exception e){
            System.err.println(e);
        }


        return users;
    }


    public ArrayList<Project> getProjectForUser(User u){
         System.out.println("INNE I GETPROJECTS");
        Connection conn = null;
        ArrayList<Project> projects = new ArrayList<Project>();
        try{
            conn = DriverManager.getConnection(database.getURL());

            PreparedStatement s = conn.prepareStatement(""
                    + "select project.* from project "+
                      "left join usersProject on (project.projectid=usersproject.projectid)" +
                      "left join users on (usersProject.userid=users.userid)" +
                      "where users.userid=?");
            s.setInt(1, u.getId());
            ResultSet r = s.executeQuery();
            while(r.next()){
                Project p = new Project(r.getInt(1),r.getString(2),r.getString(3),r.getString(4));
                projects.add(p);
            }

            conn.close();
            return projects;
        }catch(Exception e){
            System.err.println(e);
        }
        return projects;
    }
    /*
     * Project leaders have the role id =1
     */
    public ArrayList<User> getProjectLeaders(){
        Connection conn = null;
        ArrayList<User> users = new ArrayList<User>();
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement("select users.* from users " +
                                    "left join usersrole on(users.userid=usersrole.userid) "+
                                    "where usersrole.roleid = 1");
            ResultSet r = s.executeQuery();
            while(r.next()){
                users.add(new User(r.getInt(1),r.getString(2),r.getString(3)));
            }
        }catch(Exception e){
            System.err.println(e);
        }
        return users;
    }
    /*
     * Secretaries hav the role id =2
     */
    public ArrayList<User> getSecretaries(){
        Connection conn = null;
        ArrayList<User> users = new ArrayList<User>();
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement("select users.* from users " +
                                    "left join usersrole on(users.userid=usersrole.userid) "+
                                    "where usersrole.roleid = 2");
            ResultSet r = s.executeQuery();
            while(r.next()){
                users.add(new User(r.getInt(1),r.getString(2),r.getString(3)));
            }
        }catch(Exception e){
            System.err.println(e);
        }
        return users;
    }
    /*
     * Fasilitators have the role id = 3
     */
    
    public ArrayList<User> getFasilitator(){
        Connection conn = null;
        ArrayList<User> users = new ArrayList<User>();
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement("select users.* from users " +
                                    "left join usersrole on(users.userid=usersrole.userid) "+
                                    "where usersrole.roleid = 3");
            ResultSet r = s.executeQuery();
            while(r.next()){
                users.add(new User(r.getInt(1),r.getString(2),r.getString(3)));
            }
        }catch(Exception e){
            System.err.println(e);
        }
        return users;
    }

    public User getUserForUsername(String username){
        Connection conn = null;
        User user = new User();
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement("select * from users where userName=?");
            s.setString(1, username);
            ResultSet r = s.executeQuery();
            while(r.next()){
                user = new User(r.getInt(1),r.getString(2),r.getString(3));
                conn.close();
                return user;
            }
        }catch(Exception e){
            System.err.println(e);
        }
        return user;
    }

    public boolean logInn(String username, String password){
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement("select * from users where userName=? and passWord=?");
            s.setString(1, username);
            s.setString(2, password);
            ResultSet r = s.executeQuery();
            while(r.next()) {
                conn.close();
                return true;
            }
        }catch(Exception e){
            System.err.println(e);
        }
        return false;
    }

}
